﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Text;
using System.Web;
using System.Xml;
using System.IO;
using Newtonsoft.Json;

namespace AspJqGrid
{
    public class AjaxData : IHttpHandler
    {
        private string searchCase = string.Empty;                     //动态条件

        public void ProcessRequest(HttpContext context)
        {            
            if (context.Request.QueryString["action"] != null)
            {
                string actions = context.Request.QueryString["action"].ToString();
                switch (actions)
                {
                    #region 查询
                    case "view":
                        if (context.Request.QueryString["_search"].ToString() == "false")
                        {
                            context.Response.Write(jsonString(context));
                        }                        
                        else
                        {
                            //反序列化json
                            string conditions = HttpUtility.UrlDecode(context.Request.QueryString["filters"].ToString());
                            jqGridFilter filters = (jqGridFilter)JavaScriptConvert.DeserializeObject(conditions, typeof(jqGridFilter));                            
                            if (filters.GroupOp == "AND")
                            {
                                foreach(jqGridFilterRules rules in filters.JqGridFilterRulesList)
                                {
                                    switch (rules.Op)
                                    {
                                        case "cn":
                                            searchCase += " and " + rules.Field + " like '%" + rules.Data + "%'";
                                            break;
                                        case "ge":
                                            searchCase += " and " + rules.Field + ">='" + rules.Data + "'";
                                            break;
                                        case "le":
                                            searchCase += " and " + rules.Field + "<='" + rules.Data + "'";
                                            break;
                                        default:
                                            break;
                                    }
                                }
                            }
                            else
                            {
                                searchCase += " and (1<>1";
                                foreach (jqGridFilterRules rules in filters.JqGridFilterRulesList)
                                {
                                    switch (rules.Op)
                                    {
                                        case "cn":
                                            searchCase += " or " + rules.Field + " like '%" + rules.Data + "%'";
                                            break;
                                        case "ge":
                                            searchCase += " or " + rules.Field + ">='" + rules.Data + "'";
                                            break;
                                        case "le":
                                            searchCase += " or " + rules.Field + "<='" + rules.Data + "'";
                                            break;
                                        default:
                                            break;
                                    }
                                }
                                searchCase += ")";
                            }
                            context.Response.Write(jsonString(context));
                        }
                    #endregion
                        break;
                    #region 添加，修改，删除
                    case "edit":
                        string tableName = context.Request.QueryString["tablename"].ToString();
                        string idKey = string.Empty;
                        string oper = context.Request.Form["oper"].ToString();                        
                        string xmlPath = System.AppDomain.CurrentDomain.BaseDirectory + tableName + ".xml";
                        StringBuilder sb;
                        XmlDocument xmlDoc = new XmlDocument();
                        XmlNodeList xnList = xmlDoc.SelectNodes("root//columns");
                        xmlDoc.Load(xmlPath);
                        foreach (XmlNode xn in xnList)
                        {
                            if (xn.Attributes["IsIdentity"] != null)
                            {
                                idKey = xn.Attributes["name"].Value;
                            }
                        }

                        switch (oper)
                        {
                            case "edit":
                                sb = new StringBuilder();
                                sb.Append("update " + tableName + " set ");
                                foreach (XmlNode xn in xnList)
                                {
                                    if (xn.Attributes["IsIdentity"] == null || xn.Attributes["IsIdentity"].Value == "False")
                                    {
                                        if (context.Request.Form[xn.Attributes["name"].Value].ToString() != string.Empty)
                                        {
                                            if (xn.Attributes["sorttype"].Value == "int" || xn.Attributes["sorttype"].Value == "float")
                                            {
                                                sb.Append(xn.Attributes["name"].Value + "=" + HttpUtility.UrlDecode(context.Request.Form[xn.Attributes["name"].Value].ToString()) + ",");
                                            }
                                            else
                                            {
                                                sb.Append(xn.Attributes["name"].Value + "='" + HttpUtility.UrlDecode(context.Request.Form[xn.Attributes["name"].Value].ToString()) + "',");
                                            }
                                        }
                                    }
                                }
                                sb.Remove(sb.Length - 1, 1);
                                sb.Append(" where id=" + context.Request.Form[idKey].ToString());
                                DBUtility.DbHelperSQL.ExecuteSql(sb.ToString());
                                break;
                            case "add":
                                sb = new StringBuilder();
                                StringBuilder fieldstr = new StringBuilder();
                                StringBuilder valuestr = new StringBuilder();
                                foreach (XmlNode xn in xnList)
                                {
                                    if (xn.Attributes["IsIdentity"] == null || xn.Attributes["IsIdentity"].Value == "False")
                                    {
                                        if (context.Request.Form[xn.Attributes["name"].Value].ToString() != string.Empty)
                                        {
                                            fieldstr.Append(xn.Attributes["name"].Value + ",");
                                            if (xn.Attributes["sorttype"].Value == "int" || xn.Attributes["sorttype"].Value == "float")
                                            {
                                                valuestr.Append(HttpUtility.UrlDecode(context.Request.Form[xn.Attributes["name"].Value].ToString()) + ",");
                                            }
                                            else
                                            {
                                                valuestr.Append("'" + HttpUtility.UrlDecode(context.Request.Form[xn.Attributes["name"].Value].ToString()) + "',");
                                            }
                                        }
                                    }
                                }
                                fieldstr.Remove(fieldstr.Length - 1, 1);
                                valuestr.Remove(valuestr.Length - 1, 1);
                                sb.Append("insert into " + tableName + " (");
                                sb.Append(fieldstr.ToString());                               
                                sb.Append(") values (");
                                sb.Append(valuestr.ToString());
                                sb.Append(")");
                                DBUtility.DbHelperSQL.ExecuteSql(sb.ToString());
                                break;
                            case "del":
                                string sql="delete from "+tableName+" where id="+context.Request.Form[idKey].ToString();
                                DBUtility.DbHelperSQL.ExecuteSql(sql);
                                break;
                            default:
                                break;
                        }
                        #endregion
                        //context.Response.Write(jsonString(context));
                        break;
                    default:
                        break;
                }
            }
        }

        public string jsonString(HttpContext context)
        {
            //params from Request.QueryString
            string tableName = context.Request.QueryString["tablename"].ToString();
            string xmlPath = System.AppDomain.CurrentDomain.BaseDirectory + "config/" + tableName + ".xml";
            int _currentPage = int.Parse(context.Request.QueryString["page"].ToString());    // get the context.Requested page
            int _pageStep = int.Parse(context.Request.QueryString["rows"].ToString());     // get how many rows we want to have into the grid
            string sidx = context.Request.QueryString["sidx"].ToString();        // get index row - i.e. user click to sort
            string sord = context.Request.QueryString["sord"].ToString();      // get the direction

            //params from XML
            XmlDocument xmlDoc = new XmlDocument();
            xmlDoc.Load(xmlPath);
            XmlNode xnRoot = xmlDoc.SelectSingleNode("root");
            string fields = xnRoot.Attributes["fields"].Value;
            string fixCondition = string.Empty;                            //固定条件
            if (xnRoot.Attributes["fixCondition"] != null) fixCondition = xnRoot.Attributes["fixCondition"].Value;

            DataTable dt = DBUtility.DbHelperSQL.GetDataByPager(fields, tableName, " 1=1 " + fixCondition + searchCase, sidx + " " + sord, (_currentPage - 1), _pageStep);
            int _recordCount = DBUtility.DbHelperSQL.GetRecordCount(tableName, " 1=1 " + fixCondition + searchCase);
            int _totalPages;
            if (_recordCount % _pageStep == 0)
            {
                _totalPages = _recordCount / _pageStep;
            }
            else
            {
                _totalPages = _recordCount / _pageStep + 1;
            }
            if (_recordCount == 0)
            {
                return string.Empty;
            }
            else
            {
                return DataTableToJson(dt, _currentPage, _totalPages, _recordCount, xmlDoc);
            }
        }

        public string DataTableToJson(DataTable dt, int page, int total, int records, XmlDocument xmlDoc)
        {
            XmlNodeList xnList = xmlDoc.SelectNodes("root//columns");
            string idKey = string.Empty;
            foreach (XmlNode xn in xnList)
            {
                if (xn.Attributes["IsIdentity"] != null)
                {
                    idKey = xn.Attributes["name"].Value;
                }
            }
            StringBuilder sb = new StringBuilder();
            sb.Append("{");
            sb.Append("\"page\":" + page + ",");
            sb.Append("\"total\":" + total + ",");
            sb.Append("\"records\":" + records + ",");
            //-----------rows build
            sb.Append("\"rows\":[");
            foreach (DataRow dr in dt.Rows)
            {
                sb.Append("{\"id\":\"" + dr[idKey] + "\",");
                sb.Append("\"cell\":[");
                //-----------columns build
                foreach (XmlNode xn in xnList)
                {
                    if (xn.Attributes["name"].Value != idKey)
                    {
                        string values = dr[xn.Attributes["name"].Value].ToString();
                        if (values.IndexOf("\n") >= 0)  //解决JSON字符串含回车时的“未终止的字符串常量”问题
                        {
                            values = values.Replace("\n", "\\n");
                        }
                        else if (values.IndexOf('"') >= 0)  //解决"造成json字符串断层的问题，使用中文的”解决或单引号解决
                        {
                            values = values.Replace('"', '“');
                        }
                        if (xn.Attributes["sorttype"] != null)
                        {
                            switch (xn.Attributes["sorttype"].Value)
                            {
                                case "data":
                                    if (dr[xn.Attributes["name"].Value] != DBNull.Value)
                                    {
                                        sb.Append("\"" + DateTime.Parse(values) + "\",");
                                    }
                                    else
                                    {
                                        sb.Append("\" \",");
                                    }
                                    break;
                                case "float":
                                    if (dr[xn.Attributes["name"].Value] != DBNull.Value)
                                    {
                                        sb.Append("\"" + Decimal.Parse(values).ToString("0.00") + "\",");
                                    }
                                    else
                                    {
                                        sb.Append("\" \",");
                                    }
                                    break;
                                default:
                                    sb.Append("\"" + values + "\",");
                                    break;
                            }
                        }
                        else
                        {
                            sb.Append("\"" + values + "\",");
                        }
                    }
                }
                sb.Remove(sb.Length - 1, 1);
                //-----------end columns build
                sb.Append("]");
                sb.Append("},");
            }
            sb.Remove(sb.ToString().Length - 1, 1);
            sb.Append("]");
            //----------end rows build
            sb.Append("}");
            return sb.ToString();
        }

        public bool IsReusable
        {
            get
            {
                return false;
            }
        }
    }
}
